package com.study.model.jdao;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.study.model.pojo.Changes;

public interface ChangesJdao extends CrudRepository<Changes, Integer>{
	
	/**
	 * 根据经理ID查询待离职办事宜*/
	@Query("SELECT c FROM Changes c WHERE c.type=2 and c.state=0 and c.eid IN (SELECT e.eid FROM Emp e WHERE e.job='stuff' AND e.deptno=(SELECT e.deptno FROM e WHERE e.eid=?1))")
	public List<Changes> findAdmiss(int eid);
	
	/**
	 * 根据经理ID查询待调职职办事宜(离职，调出本部门)*/
	@Query("SELECT c FROM Changes c WHERE c.type=1 and c.state=0 and c.eid IN (SELECT e.eid FROM Emp e WHERE e.job='stuff' AND e.deptno=(SELECT e.deptno FROM e WHERE e.eid=?1))")
	public List<Changes> findTransfer(int eid);
	/**
	 * 根据经理ID查询待调职职办事宜(调入本部门)*/
	@Query("SELECT c FROM Changes c WHERE c.type=1 and c.state=1 and c.subdno =(SELECT e.deptno FROM Emp e WHERE e.eid=?1)")
	public List<Changes> findTransfer2(int eid);
	/**
	 * boss查询待离职办事宜*/
	@Query("SELECT c FROM Changes c WHERE c.type=2 and c.state=1")
	public List<Changes> bfindAdmiss();
	
	/**
	 * boss查询待调职办事宜*/
	@Query("SELECT c FROM Changes c WHERE c.type=1 and c.state=3")
	public List<Changes> bfindTransfer();

}
